import os
import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils import column_index_from_string
from datetime import datetime
from typing import Dict, List, Union

# 定义常量
SKIP_ROWS_LIST = 2
SKIP_ROWS_SRC = 3
TITLE_ROW = 2
DATA_START_ROW = 5

# 列字母常量
COLUMN_LETTERS = {
    'Y': 'Y', 'Z': 'Z', 'AA': 'AA', 'AB': 'AB', 'AC': 'AC', 'AD': 'AD',
    'F': 'F', 'S': 'S', 'D': 'D', 'E': 'E', 'P': 'P',
    'A': 'A', 'AF': 'AF', 'J': 'J', 'V': 'V', 'X': 'X', 'AJ': 'AJ'
}

# 列映射
COLUMN_MAPPING = {
    # 映射员工所在组织列到 Excel 表的 B 列
    'org': 'B',
    # 映射员工工作目标列到 Excel 表的 C 列
    'obj': 'C',
    # 映射员工姓名列到 Excel 表的 D 列
    'name': 'D',
    # 映射本月应工作天数列到 Excel 表的 E 列 (应工作天数)
    'work_days': 'E',
    # 映射加班小时数列到 Excel 表的 F 列 (加班)
    'add_hours': 'F',
    # 映射公共假期总和列到 Excel 表的 G 列 (婚假产假)
    'public_holiday': 'G',
    # 映射非工作天数列到 Excel 表的 H 列 (旷工)
    'no_work_days': 'H',
    # 映射折后事假天数列到 Excel 表的 I 列 (事假)
    'thing_holiday': 'I',
    # 映射折后病假天数列到 Excel 表的 J 列 (病假)
    'sick_leave': 'J',
    # 映射丧假天数列到 Excel 表的 K 列  (丧假)
    'bere_leave': 'K',
    # 映射实际工作天数列到 Excel 表的 L 列 (实际工作天数)
    'true_work_days': 'L',
    # 映射迟到天数列到 Excel 表的 M 列 (迟到)
    'late_days': 'M',
    # 映射备注信息列到 Excel 表的 O 列 (备注)
    'remake': 'O',
    # 映射员工序号列到 Excel 表的 A 列 (序号)
    'serial_number': 'A'
}

class AttendanceUtils:
    @staticmethod
    def ensure_file_exists(file_path: str) -> None:
        """确保文件存在，若不存在则抛出 FileNotFoundError 异常"""
        if not os.path.exists(file_path):
            raise FileNotFoundError(f"文件 {file_path} 不存在，请检查文件路径或文件名。")

    @staticmethod
    def ensure_folder_exists(folder: str) -> None:
        """确保文件夹存在，如果不存在则创建"""
        if not os.path.exists(folder):
            os.makedirs(folder)

    @staticmethod
    def get_cell_value(row: pd.Series, col_letter: str) -> Union[str, int, float]:
        """获取指定列的单元格值"""
        return row.iloc[AttendanceUtils.get_col_index(col_letter)]

    @staticmethod
    def is_date(text: str) -> bool:
        """判断文本是否为日期格式"""
        try:
            datetime.strptime(text, '%Y-%m-%d')
            return True
        except ValueError:
            return False

    @staticmethod
    def convert_to_numeric(value: Union[str, int, float, None], default: Union[int, float] = 0, dtype: type = float) -> Union[int, float]:
        """
        将值转换为指定的数据类型，如果值为 NaN 或字符串，尝试转换为数值类型。
        如果转换失败，则返回默认值。
        """
        if pd.isna(value) or isinstance(value, str):
            try:
                return dtype(value) if isinstance(value, str) else default
            except ValueError:
                return default
        return value

    @staticmethod
    def get_leave(holiday: Union[int, float], arge: int, isTry: bool = False) -> float:
        """获取折后假期"""
        if pd.isna(holiday):
            return 0
        if isTry:
            return round(holiday * 0.6, 2)
        if arge < 2:
            return round(holiday * 0.4, 2)
        elif 2 <= arge < 4:
            return round(holiday * 0.3, 2)
        elif 4 <= arge < 6:
            return round(holiday * 0.2, 2)
        elif 6 <= arge < 8:
            return round(holiday * 0.1, 2)
        elif arge >= 8:
            return round(holiday * 0, 2)
        return 0

    @staticmethod
    def get_col_index(letter: str) -> int:
        """获取列索引"""
        return column_index_from_string(letter) - 1

    @staticmethod
    def get_public_holiday(chunk: pd.DataFrame, cols: List[str]) -> float:
        """计算公共假期总和"""
        return sum(AttendanceUtils.convert_to_numeric(chunk.iloc[0, AttendanceUtils.get_col_index(col)]) for col in cols)

    @staticmethod
    def get_true_work_days(default_work_days: int, no_work_days: float, thing_holiday: float, sick_leave: float, worker_age: int, is_try: bool) -> float:
        """
        计算实际工作天数。

        Args:
            default_work_days (int): 本月应工作天数。
            no_work_days (float): 非工作天数。
            thing_holiday (float): 事假天数。
            sick_leave (float): 病假天数。
            worker_age (int): 员工工龄。
            is_try (bool): 是否为试用期员工。

        Returns:
            float: 实际工作天数。
        """
        # return f"{default_work_days} - {no_work_days} - {thing_holiday} - {AttendanceUtils.get_leave(sick_leave, worker_age, is_try)}"
        return default_work_days - no_work_days - thing_holiday - AttendanceUtils.get_leave(sick_leave, worker_age, is_try)

    @staticmethod
    def process_remake(chunk: pd.DataFrame, start_col: str) -> str:
        """处理备注信息"""
        start_index = AttendanceUtils.get_col_index(start_col)
        return "\n".join(
            cell_value.replace(',', '\n')
            for col_index in range(start_index, len(chunk.columns))
            if isinstance(cell_value := chunk.iloc[0, col_index], str) and ("假" in cell_value or "调休" in cell_value)
        )

    @staticmethod
    def build_worker_dict(list_file: str) -> Dict[str, Dict[str, Union[str, int, float, bool]]]:
        """构建员工字典"""
        worker_dict = {}
        df = pd.read_excel(list_file)
        for index, row in df.iterrows():
            if index < SKIP_ROWS_LIST:
                continue
            name = AttendanceUtils.get_cell_value(row, COLUMN_LETTERS['F'])
            worker_dict[name] = {
                'name': name,
                'worker_age': AttendanceUtils.get_cell_value(row, COLUMN_LETTERS['S']),
                'org': AttendanceUtils.get_cell_value(row, COLUMN_LETTERS['D']),
                'obj': AttendanceUtils.get_cell_value(row, COLUMN_LETTERS['E']),
                'is_try': AttendanceUtils.is_date(str(AttendanceUtils.get_cell_value(row, COLUMN_LETTERS['P'])))
            }
        return worker_dict

    @staticmethod
    def process_data_chunks(src_file: str, worker_dict: Dict[str, Dict[str, Union[str, int, float, bool]]], work_days: int, output_text) -> List[Dict[str, Union[str, int, float]]]:
        """处理数据块"""
        data_chunks = []
        try:
            df = pd.read_excel(src_file)
            for index, row in df.iterrows():
                if index < SKIP_ROWS_SRC:
                    continue
                name = AttendanceUtils.get_cell_value(row, COLUMN_LETTERS['A'])
                try:
                    item = worker_dict[name]
                    # 设置本月应工作天数
                    item['work_days'] = work_days
                    # 获取并转换加班小时数，若无法转换则默认为 0
                    item['add_hours'] = AttendanceUtils.convert_to_numeric(AttendanceUtils.get_cell_value(row, COLUMN_LETTERS['AF']), 0, float)
                    # 计算并设置公共假期总和
                    item['public_holiday'] = AttendanceUtils.get_public_holiday(pd.DataFrame(row).T, [COLUMN_LETTERS[col] for col in ['Y', 'Z', 'AA', 'AB', 'AC', 'AD']])
                    # 获取并转换非工作天数，若无法转换则默认为 0
                    item['no_work_days'] = AttendanceUtils.convert_to_numeric(AttendanceUtils.get_cell_value(row, COLUMN_LETTERS['S']), 0, float)
                    # 获取并转换事假天数，若无法转换则默认为 0
                    thing_holiday = AttendanceUtils.convert_to_numeric(AttendanceUtils.get_cell_value(row, COLUMN_LETTERS['V']), 0, float)
                    # 计算并设置折后事假天数，保留两位小数
                    item['thing_holiday'] = round(thing_holiday / 8, 2)
                    # 获取并转换病假天数，若无法转换则默认为 0
                    sick_leave = AttendanceUtils.convert_to_numeric(row.iloc[AttendanceUtils.get_col_index(COLUMN_LETTERS['X'])], 0, float)
                    # 计算并设置折后病假天数，保留两位小数
                    item['sick_leave'] = round(sick_leave / 8, 2)
                    # 获取并转换丧假天数，若无法转换则默认为 0
                    item['bere_leave'] = AttendanceUtils.convert_to_numeric(AttendanceUtils.get_cell_value(row, COLUMN_LETTERS['AD']), 0, float)
                    # 计算并设置实际工作天数
                    item['true_work_days'] = AttendanceUtils.get_true_work_days(work_days, item['no_work_days'], item['thing_holiday'], item['sick_leave'], item['worker_age'], item['is_try'])
                    # 获取并转换迟到天数，若无法转换则默认为 0
                    item['late_days'] = AttendanceUtils.convert_to_numeric(AttendanceUtils.get_cell_value(row, COLUMN_LETTERS['J']), 0, float)
                    # 处理并设置备注信息
                    item['remake'] = AttendanceUtils.process_remake(pd.DataFrame(row).T, COLUMN_LETTERS['AJ'])
                except KeyError:
                    output_text.insert('end', f"未找到 {name} 的信息，请检查姓名是否正确。\n")
                data_chunks.append(item)
        except pd.errors.ParserError:
            output_text.insert('end', f"解析 {src_file} 文件时出现错误，请检查文件格式。\n")
        except Exception as e:
            output_text.insert('end', f"读取文件时出现未知错误: {e}\n")
            output_text.insert('end', str(e))
        return data_chunks

    @staticmethod
    def write_data_to_sheet(sheet, data_chunks: List[Dict[str, Union[str, int, float]]], column_mapping: Dict[str, str], start_row: int = DATA_START_ROW) -> None:
        """写入数据到工作表"""
        for r_idx, item in enumerate(data_chunks, start=start_row):
            item['serial_number'] = r_idx - start_row + 1
            for key, value in item.items():
                col_letter = column_mapping.get(key)
                if col_letter:
                    col_index = AttendanceUtils.get_col_index(col_letter) + 1
                    if value in [0, "0"]:
                        value = None
                    sheet.cell(row=r_idx, column=col_index, value=value)

    @staticmethod
    def write_to_excel(report_file: str, work_month: str, data_chunks: List[Dict[str, Union[str, int, float]]], column_mapping: Dict[str, str], work_days: int, worker_dict: Dict[str, Dict[str, Union[str, int, float, bool]]]) -> None:
        """写入数据到 Excel 文件"""
        wb = load_workbook(report_file)
        last_sheet = wb[wb.sheetnames[-1]]
        new_sheet = wb.copy_worksheet(last_sheet)
        new_sheet.title = work_month

        for row in new_sheet.iter_rows(min_row=DATA_START_ROW):
            for cell in row:
                cell.value = None

        new_sheet.cell(row=TITLE_ROW, column=1).value = f'{work_month}份员工考勤确认表'

        # default_item = {
        #     'name': '',
        #     'org': '',
        #     'obj': '',
        #     'work_days': work_days,
        #     'add_hours': 0,
        #     'public_holiday': 0,
        #     'no_work_days': 0,
        #     'thing_holiday': 0,
        #     'sick_leave': 0,
        #     'bere_leave': 0,
        #     'true_work_days': work_days,
        #     'late_days': 0,
        #     'remake': ''
        # }

        # extra_names = ['杨小华', '李梦红', '程红波']
        # for name in extra_names:
        #     dict_other = worker_dict.get(name)
        #     if dict_other:
        #         item_other = default_item.copy()
        #         item_other['name'] = dict_other['name']
        #         item_other['org'] = dict_other['org']
        #         item_other['obj'] = dict_other['obj']
        #         data_chunks.append(item_other)

        AttendanceUtils.write_data_to_sheet(new_sheet, data_chunks, column_mapping)
        wb.save(report_file)
